const crypto = require('crypto');
const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const app = express();
app.use(express.json());

let db = new sqlite3.Database('./database.db', (err) => {
    if (err) {
        console.error(err.message);
    }
    console.log('Connected to the database.');
});

db.run(`CREATE TABLE IF NOT EXISTS token(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    out_time text NOT NULL,
    token text NOT NULL
);`);

db.run(`CREATE TABLE IF NOT EXISTS token_base(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    kk text NOT NULL,
    num text NOT NULL
);`);

db.run(`CREATE TABLE IF NOT EXISTS data(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    create_time text NOT NULL,
    update_time text NOT NULL,
    title text NOT NULL,
    description text NOT NULL,
    content text NOT NULL
);`);

db.run(`CREATE TABLE IF NOT EXISTS subdata(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    create_time text NOT NULL,
    update_time text NOT NULL,
    data_id text NOT NULL,
    title text NOT NULL,
    description text NOT NULL,
    content text NOT NULL
);`);

app.get('/', function(req, res) {
    res.sendFile(path.join(__dirname, 'public/index.html'));
});

app.get('/data', (req, res) => {
    const page = req.query.page || 1; // 默认为第一页
    const limit = req.query.limit || 10; // 默认每页10条数据
    const offset = (page - 1) * limit;
    const title = req.query.title || ''; // 默认为空字符串

    const sql = `SELECT id, create_time, update_time, title FROM data WHERE title LIKE ? LIMIT ${limit} OFFSET ${offset}`;
    db.all(sql, [`%${title}%`], (err, rows) => {
        if (err) {
            throw err;
        }
        db.get('SELECT COUNT(*) as total FROM data WHERE title LIKE ?', [`%${title}%`], (err, countRow) => {
            if (err) {
                throw err;
            }
            res.json({ data: rows, page: page, total: countRow.total });
        });
    });
});


app.post('/data', (req, res) => {
    const sql = 'INSERT INTO data (create_time, update_time, title, description, content) VALUES (?,?,?,?,?)';
    const now = new Date().toISOString();
    const data = [now, now, req.body.title, req.body.description, req.body.content];
    db.run(sql, data, function (err) {
        if (err) {
            return console.error(err.message);
        }
        res.json({ id: this.lastID });
    });
});


// 更新数据
app.put('/data/:id', (req, res) => {
    const sql = 'UPDATE data SET time = ?, title = ?, description = ?, content = ? WHERE id = ?';
    const data = [req.body.time, req.body.title, req.body.description, req.body.content, req.params.id];
    db.run(sql, data, function (err) {
        if (err) {
            return console.error(err.message);
        }
        res.json({ rowsAffected: this.changes });
    });
});

// 删除数据
app.delete('/data/:id', (req, res) => {
    const sql = 'DELETE FROM data WHERE id = ?';
    db.run(sql, req.params.id, function (err) {
        if (err) {
            return console.error(err.message);
        }
        res.json({ rowsAffected: this.changes });
    });
});

// 添加接口
app.post('/token', (req, res) => {
    const sql = 'INSERT INTO token (out_time, token) VALUES (?,?)';
    const token = crypto.createHash('md5').update(`${req.body.kk}/${req.body.num}`).digest('hex');
    const now = new Date();
    const out_time = new Date(now.getTime() + 24 * 60 * 60 * 1000).toISOString(); // 当前时间的1天后
    const data = [out_time, token];
    db.run(sql, data, function(err) {
      if (err) {
        return console.error(err.message);
      }
      res.json({ token: token });
    });
  });

// 编辑接口
app.put('/token/:id', (req, res) => {
    const sql = 'UPDATE token SET out_time = ?, token = ? WHERE id = ?';
    const now = new Date();
    const out_time = new Date(now.getTime() + 24 * 60 * 60 * 1000).toISOString(); // 当前时间的1天后
    const data = [out_time, req.body.token, req.params.id];
    db.run(sql, data, function (err) {
        if (err) {
            return console.error(err.message);
        }
        res.json({ id: req.params.id });
    });
});

// 删除接口
app.delete('/token/:id', (req, res) => {
    const sql = 'DELETE FROM token WHERE id = ?';
    db.run(sql, req.params.id, function (err) {
        if (err) {
            return console.error(err.message);
        }
        res.json({ id: req.params.id });
    });
});

const port = process.env.PORT || 3000;
app.listen(port, () => console.log(`Server is running on port ${port}`));
